﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace Gacn.ControleAlunos.Library.DataAccess
{
    public class RelatorioDataAccess
    {
        public static DataTable Presencas()
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM [vwTodasPresencas]";
            command.CommandType = CommandType.Text;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }

        public static DataTable PresencasDoDia(DateTime data)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;
            StringBuilder sb = new StringBuilder();

            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();

            command.CommandText = "SELECT  [idPessoa],[NomePessoa],[idPresenca],[idTurmaCalendario],[obsPresenca],[dtHrPresenca],[IdTurma],[IdCurso],[CodTurma],[nrSala],[idEscola],[NomeEscola],[Bairro],[idCidade],[idEstado] FROM [vwTodasPresencas] WHERE DATEADD(dd, 0, DATEDIFF(dd, 0,dtHrPresenca)) = @Data";
            command.CommandType = CommandType.Text;


            command.Parameters.Add("@Data", SqlDbType.DateTime).Value = data.Date;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }

        public static DataTable PresencasPorDataPorEscola(DateTime data, int EscolaID)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;
            StringBuilder sb = new StringBuilder();

            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();

            command.CommandText = "SELECT  [idPessoa],[NomePessoa],[idPresenca],[idTurmaCalendario],[obsPresenca],[dtHrPresenca],[IdTurma],[IdCurso],[CodTurma],[nrSala],[idEscola],[NomeEscola],[Bairro],[idCidade],[idEstado] FROM [vwTodasPresencas] WHERE DATEADD(dd, 0, DATEDIFF(dd, 0,dtHrPresenca)) = @Data AND idEscola = @EscolaID";
            command.CommandType = CommandType.Text;


            command.Parameters.Add("@Data", SqlDbType.DateTime).Value = data.Date;
            command.Parameters.Add("@EscolaID", SqlDbType.Int).Value = EscolaID;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }

        public static DataTable DatasDeAulaDoAno(int ano)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;

            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();

            command.CommandText = "SELECT DISTINCT DATEADD(dd, 0, DATEDIFF(dd, 0,dtHrPresenca)) AS dtPresenca FROM [vwTodasPresencas] WHERE DATEADD(dd, 0, DATEDIFF(dd, 0,dtHrPresenca)) > @Ano ORDER BY DATEADD(dd, 0, DATEDIFF(dd, 0,dtHrPresenca))";
            command.CommandType = CommandType.Text;

            command.Parameters.Add("@Ano", SqlDbType.VarChar).Value = ano;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }

        public static DataTable AlunosDadosIncompletos()
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;

            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();

            command.CommandText = "SELECT * FROM vwAlunosDadosIncompletos_teste";
            command.CommandType = CommandType.Text;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }

        public static DataTable DadosAlunosParaCracha()
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM [vwDadosAlunosParaCracha]";
            command.CommandType = CommandType.Text;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }
    }
}
